Efficient mechanism for managing hierarchical relationships in a relational database system

ABSTRACT

A method and apparatus for managing hierarchical relationships in a relational database system is provided. An “orderkey” data type, which is native within a relational database system, is disclosed. The orderkey type is designed to contain values that represent the position of an entity relative to the positions of other entities within a hierarchy. Such values represent hierarchical relationships between those entities. Values that are of the orderkey type have properties that allow hierarchy-oriented functions to be performed in an especially efficient manner. Database functions, which operate on and/or produce values that are of the orderkey type, are also disclosed. Such functions can be placed within SQL statements that a database server executes, for example. In response to executing SQL statements that contain such functions, the database server performs hierarchy-oriented operations in a highly efficient manner.

RELATED APPLICATIONS

This application is a continuation of U.S. patent application Ser. No.11/932,423, filed Oct. 31, 2007, the entire contents of which is herebyincorporated by reference as if fully set forth herein. The applicanthereby rescinds any disclaimer of claim scope in the parent applicationand the prosecution history thereof and advises the Office that a claimpresented herein may be broader in at least some respects than allclaims presented in the parent application and the prosecution historythereof.

The present application is related to U.S. patent application Ser. No.10/884,311, which was filed on Jul. 2, 2004, which is titled “INDEX FORACCESSING XML DATA,” and which is incorporated by reference for allpurposes as though fully disclosed herein.

FIELD OF THE INVENTION

The present invention relates to relational databases, and, morespecifically, to database data types and database query languageoperators.

BACKGROUND

Parent-child relationships are very common in relational databaseschemas. For example, a database schema might indicate a relationshipbetween a manager (the “parent”) and one or more employees (the“children”) who are subordinate to that manager. For another example, adatabase schema might indicate a relationship between a purchase order(the “parent”) and one or more lines (the “children”) that are a part ofthat purchase order. Data that contains such parent-child relationshipsis called hierarchical data, since the parent-child relationships form ahierarchy.

Typically, the way that a parent-child relationship is modeled within arelational database table is to include, within the table that containsthe “child” records, a column that indicates a row identifier for a“parent” record that is related to those child records in a parent-childrelationship (in a relational database table, each row of the table mayhave a unique row identifier). For example, in an “employee” table, anemployee “Alice” might be a child record of another employee “Mel;” Melmight be Alice's manager, for example. The employee table might containa “manager ID” (i.e., the parent ID) column. In the employee table, therow containing Alice's record might contain, in the manager ID column,the row identifier for the row (in the employee table) that containsMel's record. Taking the example further, if Mel is also the manager foremployees “Vera” and “Flo,” then, in the employee table, the rowscontaining Vera's and Flo's records might also contain, in the managerID column, the row identifier for the row that contains Mel's record. IfMel himself has a manager, then the value of the manager ID in Mel'srecord might also indicate the row identifier for the row that containsMel's manager's record.

Structured Query Language (SQL) has operators and constructs that allowan interested user to formulate queries that, when executed, will (a)cause all of the children of a specified parent to be returned, (b)cause all of the descendants (i.e., the children of, the children ofthose children, etc.) of a specified parent to be returned, (c) causethe parent of a specified child to be returned, and/or (d) cause all ofthe ancestors (i.e., the parent of, the parent of that parent, etc.) ofa specified child to be returned.

Using such an approach, determining the immediate parent or children ofa specified entity may be accomplished fairly trivially using a simpleindex lookup. However, determining more extended relationships, such asall ancestors or all descendants of a specified entity, can be much morecomplicated. Although SQL contains a “CONNECT-BY” construct that permitssuch complicated operations to be expressed in a relatively compactmanner, evaluating a query that contains such a construct involves anN-step algorithm in which each step involves a separate index scan. Forexample, if a query requests all of the descendants of a specifiedentity, and if there are N levels of descendants represented in thehierarchy, then evaluating the query will involve at least N separateindex scans.

Where N is very large, evaluating such a query can be very costly interms of both time and computing resources. Database system performancecan suffer when such a query is evaluated. What is needed is a way ofobtaining, from a database, hierarchical relationship information, suchas all of the ancestors or descendants of a specified entity, withoutincurring the high costs that are associated with large numbers of indexscans. The approaches described in this section are approaches thatcould be pursued, but not necessarily approaches that have beenpreviously conceived or pursued. Therefore, unless otherwise indicated,it should not be assumed that any of the approaches described in thissection qualify as prior art merely by virtue of their inclusion in thissection.

BRIEF DESCRIPTION OF THE DRAWINGS

The present invention is illustrated by way of example, and not by wayof limitation, in the figures of the accompanying drawings and in whichlike reference numerals refer to similar elements and in which:

FIG. 1 illustrates a conceptual example of a hierarchy, according to anembodiment of the invention;

FIG. 2 illustrates a conceptual example of a relational database tablethat contains an orderkey-type column that stores varioushierarchically-related entities' corresponding orderkey values,according to an embodiment of the invention; and

FIG. 3 is a block diagram that illustrates a computer system upon whichan embodiment of the invention may be implemented.

DETAILED DESCRIPTION

In the following description, for the purposes of explanation, numerousspecific details are set forth in order to provide a thoroughunderstanding of the present invention. It will be apparent, however,that the present invention may be practiced without these specificdetails. In other instances, well-known structures and devices are shownin block diagram form in order to avoid unnecessarily obscuring thepresent invention.

Overview

According to one embodiment of the invention, a new data type, which isnative within a relational database system, is introduced. This new“orderkey” data type is designed specifically to contain values thatrepresent the position of an entity relative to the positions of otherentities within a hierarchy. Values that are of the orderkey typerepresent hierarchical relationships (e.g., parent-child andsibling-sibling relationships) between entities within a hierarchy.Values that are of the orderkey type have properties that allowhierarchy-oriented functions to be performed in an especially efficientmanner. Additionally, in one embodiment of the invention, several newdatabase functions, which operate on and/or produce values that are ofthe orderkey type, are introduced. Such functions can be placed withinSQL statements that a database server executes, for example. In responseto executing SQL statements that contain such functions, the databaseserver performs hierarchy-oriented operations in a highly efficientmanner. For example, in one embodiment of the invention, a databaseserver that executes such a function determines all descendants of aspecified entity within a hierarchy by performing a range-based scan ona B-tree index on orderkey values of the entities within that hierarchy.

Orderkey Native Data Type

According to one embodiment of the invention, a database server isenhanced to recognize and understand an “orderkey” native data type. Theorderkey data type is native to the database server in the same way thatdata types such as “number,” “date,” and “string” are native to thedatabase server. Columns of a relational database table may be specifiedto contain values that are of the orderkey type. Beneficially, theorderkey data type is capable of capturing and representing not only theimmediate parent or child of a specified entity, but also is capable ofcapturing and representing the entire “lineage” of such an entity;specifying such a lineage is not a task that other native data typeswere designed to do.

In one embodiment of the invention, the orderkey data type is an opaquedata type—opaque like a number or a string. A value that is of anorderkey data type encompasses and encapsulates the notion of thelineage of (i.e., the complete ancestry of) an entity that is specifiedin a record within a row of a relational database table. In the samemanner that columns of a relational database table can be created withtypes such as number or string, in one embodiment of the invention, suchcolumns can be created with the orderkey data type. A column having theorderkey data type stores values that are of type orderkey. In oneembodiment of the invention, values of type orderkey specify sets ofbytes.

According to one embodiment, the hierarchical order information isrepresented using a Dewey-type value. Specifically, in one embodiment,the orderkey of a node is created by appending a value to the orderkeyof the node's immediate parent, where the appended value indicates theposition, among the children of the parent node, of that particularchild node.

For example, a particular node D might be the child of a node C, whichitself might be a child of a node B that is a child of a node A. Node Dmight have the orderkey 1.2.4.3. Under such circumstances, the final “3”in the orderkey indicates that the node D is the third child of itsparent node C. Similarly, the 4 indicates that node C is the fourthchild of node B. The 2 indicates that Node B is the second child of nodeA. The leading 1 indicates that node A is the root node (i.e., has noparent).

As mentioned above, the orderkey of a child may be easily created byappending, to the orderkey of the parent, a value that corresponds tothe number of the child. Similarly, the orderkey of the parent is easilyderived from the orderkey of the child by removing the last number inthe orderkey of the child.

According to one embodiment, the composite numbers represented by eachorderkey are converted into byte-comparable values, so that amathematical comparison between two orderkeys indicates the relativeposition, within a hierarchy, of the nodes to which the orderkeyscorrespond.

For example, the node associated with the orderkey 1.2.7.7 precedes thenode associated with the orderkey 1.3.1 in a theoretical hierarchicalstructure. Thus, the database server uses a conversion mechanism thatconverts orderkey 1.2.7.7 to a first value, and to convert orderkey1.3.1 to a second value, where the first value is less than the secondvalue. By comparing the second value to the first value, the databaseserver can easily determine that the node associated with the firstvalue precedes the node associated with the second value. Variousconversion techniques may be used to achieve this result, andembodiments of the invention are not limited to any particularconversion technique.

Orderkey-Based Database Functions

In one embodiment of the invention, a database server is enhanced torecognize, understand, and execute database language functions thatinvolve values that are of the orderkey type. Database users anddatabase applications may specify these functions within SQL queries,for example. Some example functions, according to various embodiments ofthe invention, are described below. First, functions for obtaining neworderkey values, to be inserted into relational database table rows, aredescribed. After these, functions for querying existing orderkey valuesthat already exist within relational database table rows are described.

In one embodiment of the invention, a database server is designed torecognize, understand, and execute a function called “GET_NEW_OKEY.”GET_NEW_OKEY does not require any input parameters. In response toevaluating GET_NEW_OKEY, a database server returns a new value that isof type orderkey. The new value corresponds to a new “root.” As usedherein, a “root” is the entity that is represented at the root node of ahierarchy; such a root node has no parent, but may have or gain anynumber of children. Thus, in one embodiment of the invention, theevaluation of GET_NEW_OKEY causes the database server to create,conceptually, a new hierarchy and return the value (of type orderkey)that, among other qualities, corresponds to the entity that is at theroot node of that hierarchy. In typical usage, after obtaining thereturned value from GET_NEW_OKEY, a user or application would actuallycreate (if they had not already done so) a row in a relational databasetable, and would populate an appropriate column (of type orderkey) ofthat row with the returned value.

In one embodiment of the invention, a database server is designed torecognize, understand, and execute a function called “GET_FIRST CHILDOKEY.” In one embodiment of the invention, GET_FIRST_CHILD_OKEY accepts,as input, a parameter that specifies a value (of type orderkey) thatcorresponds to a parent entity. In one embodiment of the invention,GET_FIRST_CHILD_OKEY returns a value (of type orderkey) that correspondsto an entity that is the first-ordered immediate child of the parententity. Thus, in one embodiment of the invention, the children of aparticular parent are ordered relative to one another; for example, ifthere are three children of a particular parent, then one child is thefirst child, one child is the second child, and one child is the thirdchild. In typical usage, after obtaining the returned value fromGET_FIRST_CHILD_OKEY, a user or application would actually create (ifthey had not already done so), in a relational database table, a rowthat corresponded to the first child of the specified parent, and wouldpopulate an appropriate column (of type orderkey) of that row with thereturned value. The returned value would indicate, among otherqualities, that the entity represented by that row is the first-orderedchild of the entity that is represented within the parent's row.

In one embodiment of the invention, a database server is designed torecognize, understand, and execute a function called “GET_SIBLING_OKEY.”In one embodiment of the invention, GET_SIBLING_OKEY accepts, as input,a first parameter that specifies a value (of type orderkey) thatcorresponds to a higher-ordered sibling entity. In one embodiment of theinvention, GET_SIBLING_OKEY additionally accepts, as input, an optionalsecond parameter that specifies a value (of type orderkey) thatcorresponds to a lower-ordered sibling entity. In one embodiment of theinvention, GET_SIBLING_OKEY returns a value (of type orderkey) thatcorresponds to an entity that is an immediately lower-ordered sibling ofthe higher-ordered sibling (as indicated by the first parameter). In oneembodiment of the invention, if the second, optional parameter ofGET_SIBLING_OKEY has been specified, then the returned value correspondsadditionally to an immediately higher-ordered sibling of thelower-ordered sibling (as indicated by the second parameter).

For example, if the first input parameter of GET_SIBLING_OKEY specifiesthe currently first-ordered child of a particular entity, and if thesecond input parameter of GET_SIBLING_OKEY specifies the currentlysecond-ordered child of the particular entity, then GET_SIBLING_OKEYreturns a value that corresponds to a sibling (of the currently first-and second-ordered children) whose order is in between the first-orderedchild and the second-ordered child—under such circumstances, the currentsecond-ordered child essentially becomes the new third-ordered child(although, in one embodiment of the invention, the orderkey values forthe already existing children do not change), and the returned valuecorresponds to a new second-ordered child. The parent of the new siblingis the same as the parent for the siblings whose orderkey values werespecified as input parameters to GET_SIBLING_OKEY. In typical usage,after obtaining the returned value from GET_SIBLING_OKEY, a user orapplication would actually create (if they had not already done so), ina relational database table, a row that corresponded to the new sibling,and would populate an appropriate column (of type orderkey) of that rowwith the returned value.

The foregoing functions just described are for obtaining new orderkeyvalues that are to be inserted into relational database table rows.Below, functions for querying existing orderkey values that alreadyexist within relational database table rows are described.

In one embodiment of the invention, a database server is designed torecognize, understand, and execute a function called “GET_PARENT_OKEY.”In one embodiment of the invention, GET_PARENT_OKEY accepts, as input, aparameter that specifies a value (of type orderkey) that corresponds toa particular entity. In one embodiment of the invention, GET_PARENT_OKEYreturns a value (of type orderkey) that corresponds to an existingparent of the entity that corresponds to the input parameter.

In one embodiment of the invention, a database server is designed torecognize, understand, and execute a function called “GET_OKEY_LEVEL.”In one embodiment of the invention, GET_OKEY_LEVEL accepts, as input, aparameter that specifies a value (of type orderkey) that corresponds toa particular entity. In one embodiment of the invention, GET_OKEY_LEVELreturns a value (e.g., of type number) that indicates the level of thehierarchy at which the particular entity exists. This value essentiallyexpresses how many entities hierarchically intervene between (a) theentity at the root node of the hierarchy and (b) the particular entity.For example, the entity at the root node of the hierarchy might exist atlevel one. The immediate children of that entity might exist at leveltwo. The immediate children of those entities might exist at levelthree, and so on.

In one embodiment of the invention, a database server is designed torecognize, understand, and execute a function called “IS_PARENT_OF.” Inone embodiment of the invention, IS_PARENT_OF accepts, as input, twoparameters. Each such parameter specifies a value (of type orderkey)that corresponds to a separate entity. In one embodiment of theinvention, IS_PARENT_OF returns a value (e.g., of type Boolean) thatindicates either true (or “1”) or false (or “0”). In one embodiment ofthe invention, if (a) the entity that corresponds to the first parameteris the immediate parent of (b) the entity that corresponds to the secondparameter, then IS_PARENT_OF returns “true.” Otherwise, IS_PARENT_OFreturns “false.”

In one embodiment of the invention, a database server is designed torecognize, understand, and execute a function called “IS_ANCESTOR_OF.”In one embodiment of the invention, IS_ANCESTOR_OF accepts, as input,two parameters. Each such parameter specifies a value (of type orderkey)that corresponds to a separate entity. In one embodiment of theinvention, IS_ANCESTOR_OF returns a value (e.g., of type Boolean) thatindicates either true (or “1”) or false (or “0”). In one embodiment ofthe invention, if (a) the entity that corresponds to the first parameteris a hierarchical ancestor (not necessarily immediate) of (b) the entitythat corresponds to the second parameter, then IS_ANCESTOR_OF returns“true.” Otherwise, IS_ANCESTOR_OF returns “false.”

Beneficially, by using the functions described above, database users anddatabase applications do not need to formulate queries using therelatively complicated “CONNECT-BY” syntax.

Orderkey Properties

According to one embodiment of the invention, each value of typeorderkey possesses and satisfies the set of properties that aredescribed below. First, each orderkey value reflects a hierarchicalordering. This means that if a hierarchy is represented as a treestructure, and if each node in the tree structure is numbered accordingto some ordering scheme (e.g., depth-first traversal), such that eachnode has a unique number that at least partially signifies its positionin the tree structure relative to other nodes therein, then the bytes ofan orderkey value that corresponds to a particular node in the treestructure will at least represent the number that corresponds to theparticular node. Additionally, in one embodiment of the invention, if afirst entity is a child of a second entity, then the orderkey value forthe first entity's row will be larger than the orderkey value for thesecond entity's row. Similarly, in one embodiment of the invention, if afirst entity is a lower-ordered sibling of a second entity, then theorderkey value for the first entity's row will be larger than theorderkey value for the second entity's row.

Second, each particular value of type orderkey, except for a value thatcorresponds to a root node in a hierarchy, has a prefix (in bytes) thatis identical to the orderkey value of the immediate parent of the nodeto which that particular value corresponds. For example, if the orderkeyvalue for a particular entity is represented as “1,” then the orderkeyvalues for the immediate children of the particular entity might berepresented (conceptually) as “1.1,” “1.2,” “1.3,” and so on. Extendingthe example, if the orderkey value for a particular entity is “1.3,”then the orderkey values for the immediate children of the particularentity might be represented (conceptually) as “1.3.1,” “1.3.2,” “1.3.3,”and so on. Thus, each orderkey value identifies an entire hierarchicallineage of an entity to which that orderkey value corresponds. Becauseeach orderkey value identifies such a lineage, the orderkey-basedfunctions described above can be performed using relatively few indexscans.

Third, a new orderkey can be constructed such that the value of that neworderkey exists between the values of two existing orderkeys. Forexample, if the orderkey values for two orderkeys are represented(conceptually) as “1.1” and “1.2,” then, in one embodiment of theinvention, a new orderkey can be created (e.g. as a sibling of theorderkey whose value is “1.1” and/or as a child of the orderkey whosevalue is “1,” using functions discussed above) with a value that isbetween “1.1” and “1.2.” In one embodiment of the invention, in order tomake in-between orderkey creation possible, orderkey values take theform of floating-point numbers rather than integers. For example, thevalue of an orderkey that is created to be in between the orderkeys withvalues of “1.1” and “1.2” might be represented (conceptually) as “1(1½)”(which is not the same, conceptually, as “1.1.5.”—while “1.(1½)” is onthe second hierarchical level and is an immediate child of “1,” “1.1.5”is on the third hierarchical level and is an immediate child of “1.1”).

Thus, in one embodiment of the invention, each orderkey has all of theproperties that are discussed above. However, orderkeys and the valuesthereof might be encoded in a variety of different ways while stillpossessing all of these properties. Various manners of encodingorderkeys and the values thereof are within the scope of variousembodiments of the invention. In one embodiment of the invention, eachorderkey is a sequence of bytes; for example, each byte in the sequencemay correspond to a different level in a hierarchy. For a more specificexample, if a particular orderkey value was “1.3.5,” then the first byteof that orderkey might represent “1” (corresponding to the firsthierarchical level), the second byte of that orderkey might represent“3” (corresponding to the second hierarchical level), and the third byteof that orderkey might represent “5” (corresponding to the thirdhierarchical level).

Efficiencies Gained from Orderkey Properties

Due to at least some of the orderkey properties described above, in oneembodiment of the invention, many of the orderkey-based functionsdescribed above (e.g., IS_ANCESTOR_OF) can be implemented as, orre-written by a database server as, relatively simple byte-levelcomparisons. For example, to find all of the descendants of a particularentity, a database server can simply determine the set of entities, inthe hierarchy, whose orderkey values are prefixed by the orderkey valueof the particular entity; each entity in such a set is a descendant ofthe particular entity. Thus, a problem which previously would have beensolved in N steps is reduced to a range-based problem that can be solvedin fewer than N steps. A single index range scan on a B-tree can be usedto find, relatively quickly, all entities whose orderkey values have aspecified prefix.

Maximum Child Orderkey

In one embodiment of the invention, for each entity in a hierarchy, a“maximum child orderkey” is defined for that entity. The maximum childorderkey of a particular entity represents the theoretical maximum valuethat any orderkey of any child of that particular entity could have. Inone embodiment of the invention, to find all immediate children of aparticular entity in a hierarchy, a range-based index scan is performedto determine all entities whose orderkey values are both (a) greaterthan the orderkey value of the particular entity (i.e., the parent) and(b) less than or equal to the maximum child orderkey for the particularentity.

Example Hierarchical Relational Data Structures

FIG. 1 illustrates a conceptual example of a hierarchy 100, according toan embodiment of the invention. Each node in hierarchy 100 correspondsto a separate real-world entity (in this example, each node correspondsto a person in a corporate organization). Additionally, in hierarchy100, each node is labeled with a value of type orderkey—the orderkeyvalue that corresponds to that node's entity. Such values may begenerated using functions such as GET_NEW_OKEY, GET_FIRST_CHILD_OKEY,and GET_SIBLING_OKEY, as discussed above. The values shown areconceptual in nature; actual orderkey values may be in a more highlycompressed form that those shown.

At the first level of hierarchy 100, node 102 has an orderkey value of“1,” indicating that node 102 is the root node of hierarchy 100.

At the second level of hierarchy 100, node 104 has an orderkey value of“1.1,” indicating that node 104 is a child of node 102. Node 106 has anorderkey value of “1.2,” indicating that node 106 is a child of node 102and is a lower-ordered sibling of node 104. Node 108 has an orderkeyvalue of “1.3,” indicating that node 108 is a child of node 102 and is alower-ordered sibling of nodes 104 and 106.

At the third level of hierarchy 100, node 110 has an orderkey value of“1.1.1,” indicating that node 110 is a child of node 104. Node 112 hasan orderkey value of “1.1.2,” indicating that node 112 is a child ofnode 104 and is a lower-ordered sibling of node 110. Node 114 has anorderkey value of “1.3.1,” indicating that node 114 is a child of node108.

At the fourth level of hierarchy 100, node 116 has an orderkey value of“1.3.1.1,” indicating that node 116 is a child of node 114. Node 118 hasan orderkey value of “1.3.1.2,” indicating that node 118 is a child ofnode 114 and is a lower-ordered sibling of node 116. Node 120 has anorderkey value of “1.3.1.3,” indicating that node 120 is a child of node114 and is a lower-ordered sibling of nodes 116 and 118. Node 122 has anorderkey value of “1.3.1.4,” indicating that node 122 is a child of node114 and is a lower-ordered sibling of nodes 116, 118, and 120.

Such orderkey values may be inserted into an orderkey-type column of arelational database table. A B-tree index may be created on such acolumn to enable more efficient performance of hierarchy-basedoperations on the data in such a relational database table. FIG. 2illustrates a conceptual example of a relational database table 200 thatcontains an orderkey-type column that stores varioushierarchically-related entities' corresponding orderkey values,according to an embodiment of the invention.

Relational database table 200 contains four columns. Among thesecolumns, column 202 specifies values of people's names and might containvalues of type “string.” Column 204 specifies values of people's socialsecurity numbers and might contain values of type “number.” Column 206specifies values of people's birthdates and might contain values of type“date.” Column 208 specifies values of people's corresponding orderkeysand contains values of type “orderkey” (and only values of type“orderkey”).

In relational table 200, the record for “Dorian Giesler” specifies, incolumn 208, an orderkey value of “1.” Thus, “Dorian Giesler” is theentity that corresponds to node 102 in hierarchy 100.

In relational table 200, the record for “Sindy Omara” specifies, incolumn 208, an orderkey value of “1.1.” Thus, “Sindy Omara” is theentity that corresponds to node 104 in hierarchy 100.

In relational table 200, the record for “Gallagher Northey” specifies,in column 208, an orderkey value of “1.2.” Thus, “Gallagher Northey” isthe entity that corresponds to node 106 in hierarchy 100.

In relational table 200, the record for “Beatrice Stall” specifies, incolumn 208, an orderkey value of “1.1.1.” Thus, “Beatrice Stall” is theentity that corresponds to node 110 in hierarchy 100.

In relational table 200, the record for “Gardenia Nicola” specifies, incolumn 208, an orderkey value of “1.3.” Thus, “Gardenia Nicola” is theentity that corresponds to node 108 in hierarchy 100.

In relational table 200, the record for “Tanner Ream” specifies, incolumn 208, an orderkey value of “1.3.1.” Thus, “Tanner Ream” is theentity that corresponds to node 114 in hierarchy 100.

In relational table 200, the record for “Lauressa Newman” specifies, incolumn 208, an orderkey value of “1.3.1.1.” Thus, “Lauressa Newman” isthe entity that corresponds to node 116 in hierarchy 100.

In relational table 200, the record for “Opal Cavalet” specifies, incolumn 208, an orderkey value of “1.3.1.2.” Thus, “Opal Cavalet” is theentity that corresponds to node 118 in hierarchy 100.

In relational table 200, the record for “Randolf Quinn” specifies, incolumn 208, an orderkey value of “1.1.2.” Thus, “Randolf Quinn” is theentity that corresponds to node 112 in hierarchy 100.

In relational table 200, the record for “Temple Knight” specifies, incolumn 208, an orderkey value of “1.3.1.3.” Thus, “Temple Knight” is theentity that corresponds to node 120 in hierarchy 100.

In relational table 200, the record for “Geffrey Fulton” specifies, incolumn 208, an orderkey value of “1.3.1.4.” Thus, “Geffrey Fulton” isthe entity that corresponds to node 122 in hierarchy 100.

Based on the values in column 208, a database server can quicklydetermine, with reference to a B-tree index formed on column 208,various relationships between the entities that are represented inhierarchy 100. For example, the database server can quickly determinethat the children of “Dorian Giesler” are “Sindy Omara,” “GallagherNorthey,” and “Gardenia Nicola.” For another example, the databaseserver can quickly determine that the children of “Sindy Omara” are“Beatrice Stall” and “Randolf Quinn.” For another example, the databaseserver can quickly determine that the descendants of “Gardenia Nicola”are “Tanner Ream” and his children, “Lauressa Newman,” “Opal Cavalet,”“Temple Knight,” and “Geffrey Fulton.” For another example, the databaseserver can quickly determine that the ancestors of “Temple Knight” are“Tanner Ream,” “Gardenia Nicola,” and “Dorian Giesler.” For anotherexample, the database server can quickly determine that the siblings of“Gardenia Nicola” are “Sindy Omara” and “Gallagher Northey.” Thedatabase server can determine such relationships even without the use ofthe “CONNECT-BY” SQL construct.

In one embodiment of the invention, the hierarchical relationshipsbetween entities (such as the hierarchical relationships depicted inFIG. 1) are real-world relationships that indicate some realhierarchical relationship between real-world entities (such as people).For example, in hierarchy 100, parent-child relationships between nodesmay represent real-world manager-subordinate employee relationships in acorporation.

Hardware Overview

FIG. 3 is a block diagram that illustrates a computer system 300 uponwhich an embodiment of the invention may be implemented. Computer system300 includes a bus 302 or other communication mechanism forcommunicating information, and a processor 304 coupled with bus 302 forprocessing information. Computer system 300 also includes a main memory306, such as a random-access memory (RAM) or other dynamic storagedevice, coupled to bus 302 for storing information and instructions tobe executed by processor 304. Main memory 306 also may be used forstoring temporary variables or other intermediate information duringexecution of instructions to be executed by processor 304. Computersystem 300 further includes a read only memory (ROM) 308 or other staticstorage device coupled to bus 302 for storing static information andinstructions for processor 304. A storage device 310, such as a magneticdisk or optical disk, is provided and coupled to bus 302 for storinginformation and instructions.

Computer system 300 may be coupled via bus 302 to a display 312, such asa cathode ray tube (CRT), for displaying information to a computer user.An input device 314, including alphanumeric and other keys, is coupledto bus 302 for communicating information and command selections toprocessor 304. Another type of user input device is cursor control 316,such as a mouse, a trackball, or cursor direction keys for communicatingdirection information and command selections to processor 304 and forcontrolling cursor movement on display 312. This input device typicallyhas two degrees of freedom in two axes, a first axis (e.g., x) and asecond axis (e.g., y), that allows the device to specify positions in aplane.

The invention is related to the use of computer system 300 forimplementing the techniques described herein. According to oneembodiment of the invention, those techniques are performed by computersystem 300 in response to processor 304 executing one or more sequencesof one or more instructions contained in main memory 306. Suchinstructions may be read into main memory 306 from anothermachine-readable medium, such as storage device 310. Execution of thesequences of instructions contained in main memory 306 causes processor304 to perform the process steps described herein. In alternativeembodiments, hard-wired circuitry may be used in place of or incombination with software instructions to implement the invention. Thus,embodiments of the invention are not limited to any specific combinationof hardware circuitry and software.

The term “machine-readable medium” as used herein refers to any mediumthat participates in providing data that causes a machine to operationin a specific fashion. In an embodiment implemented using computersystem 300, various machine-readable media are involved, for example, inproviding instructions to processor 304 for execution. Such a medium maytake many forms, including but not limited to storage media andtransmission media. Storage media includes both non-volatile media andvolatile media. Non-volatile media includes, for example, optical ormagnetic disks, such as storage device 310. Volatile media includesdynamic memory, such as main memory 306. Transmission media includescoaxial cables, copper wire and fiber optics, including the wires thatcomprise bus 302. Transmission media can also take the form of acousticor light waves, such as those generated during radio-wave and infra-reddata communications. All such media must be tangible to enable theinstructions carried by the media to be detected by a physical mechanismthat reads the instructions into a machine.

Common forms of machine-readable media include, for example, a floppydisk, a flexible disk, hard disk, magnetic tape, or any other magneticmedium, a CD-ROM, any other optical medium, punchcards, papertape, anyother physical medium with patterns of holes, a RAM, a PROM, and EPROM,a FLASH-EPROM, any other memory chip or cartridge, a carrier wave asdescribed hereinafter, or any other medium from which a computer canread.

Various forms of machine-readable media may be involved in carrying oneor more sequences of one or more instructions to processor 304 forexecution. For example, the instructions may initially be carried on amagnetic disk of a remote computer. The remote computer can load theinstructions into its dynamic memory and send the instructions over atelephone line using a modem. A modem local to computer system 300 canreceive the data on the telephone line and use an infra-red transmitterto convert the data to an infra-red signal. An infra-red detector canreceive the data carried in the infra-red signal and appropriatecircuitry can place the data on bus 302. Bus 302 carries the data tomain memory 306, from which processor 304 retrieves and executes theinstructions. The instructions received by main memory 306 mayoptionally be stored on storage device 310 either before or afterexecution by processor 304.

Computer system 300 also includes a communication interface 318 coupledto bus 302. Communication interface 318 provides a two-way datacommunication coupling to a network link 320 that is connected to alocal network 322. For example, communication interface 318 may be anintegrated services digital network (ISDN) card or a modem to provide adata communication connection to a corresponding type of telephone line.As another example, communication interface 318 may be a local areanetwork (LAN) card to provide a data communication connection to acompatible LAN. Wireless links may also be implemented. In any suchimplementation, communication interface 318 sends and receiveselectrical, electromagnetic or optical signals that carry digital datastreams representing various types of information.

Network link 320 typically provides data communication through one ormore networks to other data devices. For example, network link 320 mayprovide a connection through local network 322 to a host computer 324 orto data equipment operated by an Internet Service Provider (ISP) 326.ISP 326 in turn provides data communication services through theworld-wide packet data communication network now commonly referred to asthe “Internet” 328. Local network 322 and Internet 328 both useelectrical, electromagnetic or optical signals that carry digital datastreams. The signals through the various networks and the signals onnetwork link 320 and through communication interface 318, which carrythe digital data to and from computer system 300, are exemplary forms ofcarrier waves transporting the information.

Computer system 300 can send messages and receive data, includingprogram code, through the network(s), network link 320 and communicationinterface 318. In the Internet example, a server 330 might transmit arequested code for an application program through Internet 328, ISP 326,local network 322 and communication interface 318.

The received code may be executed by processor 304 as it is received,and/or stored in storage device 310, or other non-volatile storage forlater execution. In this manner, computer system 300 may obtainapplication code in the form of a carrier wave.

In the foregoing specification, embodiments of the invention have beendescribed with reference to numerous specific details that may vary fromimplementation to implementation. Thus, the sole and exclusive indicatorof what is the invention, and is intended by the applicants to be theinvention, is the set of claims that issue from this application, in thespecific form in which such claims issue, including any subsequentcorrection. Any definitions expressly set forth herein for termscontained in such claims shall govern the meaning of such terms as usedin the claims. Hence, no limitation, element, property, feature,advantage or attribute that is not expressly recited in a claim shouldlimit the scope of such claim in any way. The specification and drawingsare, accordingly, to be regarded in an illustrative rather than arestrictive sense.

1. A computer-implemented method comprising: receiving, as input to apreviously defined function within a database, a value, of a particulardata type, that indicates a respective position within a hierarchy;wherein the value indicates a complete hierarchical lineage of therespective position within the hierarchy; and the function returning aresult value based on both: a predefined hierarchical relationship withrespect to the respective position within the hierarchy.
 2. The methodof claim 1, wherein the predefined hierarchical relationship is selectedfrom the group consisting of: first child, left sibling, right sibling,and parent.
 3. The method of claim 1, wherein the result value is of theparticular data type.
 4. The method of claim 1, wherein the respectiveposition is a first respective position; and wherein the result valueindicates a complete hierarchical lineage of a second respectiveposition within the hierarchy, the second respective position having thepredefined hierarchical relationship with respect to the firstrespective position.
 5. The method of claim 1, wherein the value is afirst value; wherein the respective position is a first respectiveposition, and wherein the method further comprises: receiving, as inputto the previously defined function within the database, a second value,of the particular data type, that indicates a second respective positionwithin the hierarchy; wherein the second value indicates a completehierarchical lineage of the second respective position within thehierarchy; and wherein the function returning the result value based onthe predefined hierarchical relationship with respect to the firstrespective position in the hierarchy is based on the first respectiveposition having the predefined hierarchical relationship with respect tothe second respective position.
 6. The method of claim 1, wherein thefunction has a name and the name of the function indicates thepredefined hierarchical relationship.
 7. The method of claim 1, whereinthe value is represented by a first sequence of bytes; wherein theresult value is represented by a second sequence of bytes; wherein thepredefined hierarchical relationship is parent; wherein the secondsequence of bytes is byte-wise less than the first sequence of bytes;and wherein the second sequence of bytes is a byte-wise prefix of thefirst sequence of bytes.
 8. One or more non-transitory computer-readablemedia storing one or more programs for execution by one or moreprocessors, the one or more programs having instructions configured for:receiving, as input to a previously defined function within a database,a value, of a particular data type, that indicates a respective positionwithin a hierarchy; wherein the value indicates a complete hierarchicallineage of the respective position within the hierarchy; and thefunction returning a result value based on a predefined hierarchicalrelationship with respect to the respective position within thehierarchy.
 9. The one or more non-transitory computer-readable media ofclaim 8, wherein the predefined hierarchical relationship is selectedfrom the group consisting of: first child, left sibling, right sibling,and parent.
 10. The one or more non-transitory computer-readable mediaof claim 8, wherein the result value is of the particular data type. 11.The one or more non-transitory computer-readable media of claim 8,wherein the respective position is a first respective position; andwherein the result value indicates a complete hierarchical lineage of asecond respective position within the hierarchy, the second respectiveposition having the predefined hierarchical relationship with respect tothe first respective position.
 12. The one or more non-transitorycomputer-readable media of claim 8, wherein the value is a first value;wherein the respective position is a first respective position, andwherein the instructions are further configured for: receiving, as inputto the previously defined function within the database, a second value,of the particular data type, that indicates a second respective positionwithin the hierarchy; wherein the second value indicates a completehierarchical lineage of the second respective position within thehierarchy; and wherein the function returning the result value based onthe predefined hierarchical relationship with respect to the firstrespective position in the hierarchy is based on the first respectiveposition having the predefined hierarchical relationship with respect tothe second respective position.
 13. The one or more non-transitorycomputer-readable media of claim 8, wherein the function has a name andthe name of the function indicates the predefined hierarchicalrelationship.
 14. The one or more non-transitory computer-readable mediaof claim 8, wherein the value is represented by a first sequence ofbytes; wherein the result value is represented by a second sequence ofbytes; wherein the predefined hierarchical relationship is rightsibling; and wherein the second sequence of bytes is a byte-wise greaterthan the first sequence of bytes.
 15. A computing system, comprising:one or more processors; storage media; one or more programs stored inthe storage media and configured for execution by the one or moreprocessors, the one or more programs having instructions configured for:receiving, as input to a previously defined function within a database,a value, of a particular data type, that indicates a respective positionwithin a hierarchy; wherein the value indicates a complete hierarchicallineage of the respective position within the hierarchy; and thefunction returning a result value based on a predefined hierarchicalrelationship with respect to the respective position within thehierarchy.
 16. The computing system of claim 15, wherein the predefinedhierarchical relationship is selected from the group consisting of:first child, left sibling, right sibling, and parent.
 17. The computingsystem of claim 15, wherein the result value is of the particular datatype.
 18. The computing system of claim 15, wherein the respectiveposition is a first respective position; and wherein the result valueindicates a complete hierarchical lineage of a second respectiveposition within the hierarchy, the second respective position having thepredefined hierarchical relationship with respect to the firstrespective position.
 19. The computing system of claim 15, wherein thevalue is a first value; wherein the respective position is a firstrespective position, and wherein the instructions are further configuredfor: receiving, as input to the previously defined function within thedatabase, a second value, of the particular data type, that indicates asecond respective position within the hierarchy; wherein the secondvalue indicates a complete hierarchical lineage of the second respectiveposition within the hierarchy; wherein the first value is a firstsequence of bytes; wherein the second value is a second sequence ofbytes; wherein the result value is a third sequence of bytes; whereinthe predefined hierarchical relationship is left sibling; and whereinthe third sequence of bytes is byte-wise greater than the first sequenceof bytes and byte-wise less than the second sequence of bytes.
 20. Thecomputing system of claim 15, wherein the value is represented by afirst sequence of bytes; wherein the result value is represented by asecond sequence of bytes; wherein the predefined hierarchicalrelationship is first child; wherein the second sequence of bytes isbyte-wise greater than the first sequence of bytes; and wherein thefirst sequence of bytes is a byte-wise prefix of the second sequence ofbytes.